drop database CIS;
create database CIS;

go
use CIS;

go

--CREATE TABLE GROUP
create table [Group] (
	GroupID int primary key identity,
	GroupDesc varchar(50)
)

--CREATE TABLE MODULE
create table Module (
	ModuleID int primary key identity,
	ModuleDesc varchar(50)
)

--CREATE TABLE PERMISSION
create table Permission 
(
	PermissionID int primary key identity,
	PermissionDesc varchar(50),
	GroupID int not null foreign key references [Group],
	ModuleID int not null foreign key references Module
)

--CREATE TABLE PRODUCTCATEGORY
create table ProductCategory(
	ProductCatID int primary key identity,
	ProductCatDesc varchar(50),
)

--CREATE TABLE PRODUCT
create table Product(
	ProductID varchar(20) primary key,
	ProductCatID int foreign key references ProductCategory,
	ProductName varchar(50) not null unique,
	ProductDesc varchar(50)
)

--CREATE TABLE CHANNEL
Create table Channel(
	ChannelID int identity primary key,
	ChannelDesc varchar(50)
)
--CREATE TABLE PROMOTION
create table Promotion(
	PromotionID int identity primary key,
	Discount int,
	Gift varchar(50)
)

--CREATE TABLE EMPLOYEE

create table Employee (
	EmployeeID varchar(20) primary key,
	Username varchar(50) not null unique,
	Password varchar(50) not null,
	EmployeeName varchar(50) not null,
	Gender varchar(5)not null,
	DOB datetime,
	GroupID int not null foreign key references [Group]
)

--CREATE TABLE CAMPAIGN
create table Campaign(
	CampaignID varchar(20) primary key,
	CampaignName varchar(50) not null unique,
	ProductID varchar(20) not null foreign key references Product,
	ChannelID int not null foreign key references Channel,
	PromotionID int not null foreign key references Promotion,
	EstimatedBudget money,
	EstimatedSale money,
	ActualBudget money,
	ActualSale money,
	StartDate datetime,
	EndDate datetime	
)

-- INSERT DUMMY DATA
insert into [Group] values
('Employee'),
('CampaignMaster'),
('ProductMaster'),
('Admin')

insert into Module values
('CampaignMaster'),
('ProductMaster'),
('EstimatedCampaign'),
('ActualCampaign'),
('Statistics'),
('Help'),
('Administrator')

--Normal Employee can access to Help, Statistics
--Product master can access modules related to campaign and Help, Statistics
--Product master can access modules related to product and Help, Statistics
--Admin can access all modules
insert into Permission values
('Employee', 1, 5),
('Employee', 1, 6),
('CampaignMaster', 2, 1),
('CampaignMaster', 2, 3),
('CampaignMaster', 2, 4),
('CampaignMaster', 2, 5),
('CampaignMaster', 2, 6),
('ProductMaster', 3, 2),
('ProductMaster', 3, 5),
('ProductMaster', 3, 6),
('Admin', 4, 1),
('Admin', 4, 2),
('Admin', 4, 3),
('Admin', 4, 4),
('Admin', 4, 5),
('Admin', 4, 6),
('Admin', 4, 7)

insert into ProductCategory values
('Auto And Recreation'),
('Home'),
('Life And Financial'),
('Business')

insert into Product values
('AC0001', 1, 'Auto insurance', 'Auto insurance'),
('AC0002', 1, 'Collectible Auto insurance', 'Collectible Auto insurance'),
('AC0003', 1, 'Motorcycle insurance', 'Motorcycle insurance'),
('AC0004', 1, 'Boat insurance', 'Boat insurance'),
('AC0005', 1, 'Travel Trailer insurance', 'Travel Trailer insurance'),
('AC0006', 1, 'Motor Home insurance', 'Motor Home insurance'),
('HM0001', 2, 'Homeowner insurance', 'Homeowner insurance'),
('HM0002', 2, 'Renters insurance', 'Renters insurance'),
('HM0003', 2, 'Condo insurance', 'Condo insurance'),
('HM0004', 2, 'Mobile Home insurance', 'Mobile Home insurance'),
('HM0005', 2, 'Specialty Home insurance', 'Specialty Home insurance'),
('LF0001', 3, 'Term Life insurance', 'Term Life insurance'),
('LF0002', 3, 'Whole Life insurance', 'Whole Life insurance'),
('LF0003', 3, 'Annuities', 'Annuities'),
('LF0004', 3, 'Mutual Funds', 'Mutual Funds'),
('BN0001', 4, 'Business Liability insurance', 'Business Liability insurance'),
('BN0002', 4, 'Professional Service insurance', 'Professional Service insurance'),
('BN0003', 4, 'Auto Service/Repair insurance', 'Auto Service/Repair insurance'),
('BN0004', 4, 'Home/Building service', 'Home/Building service'),
('BN0005', 4, 'Business Umbrella insurance', 'Business Umbrella insurance')

insert into Channel values
('Tele marketing'),
('Letters'),
('Signboards'),
('TV Commercials')

insert into Promotion values
(3, 'A credit card HSBC 100USD'),
(5, 'A credit card HSBC 100USD'),
(2, 'One more year insurance'),
(7, 'None')

insert into Employee values('EM0001', 'cis', 'cis','John','M','1990/1/1',1);
insert into Employee values('EM0002', 'campaign', 'campaign','Peter','M','1982/4/2',2);
insert into Employee values('EM0003', 'product', 'product','Albert','M','1980/6/5',3);
insert into Employee values('EM0004', 'admin', 'admin','Obama','M','1980/9/3',4);

insert into Campaign values
('CM0001', 'Small investment, Big insurance for life!', 'LF0001', 1, 2,20000, 120000,0,0, '2013/9/2', '2013/10/02'),
('CM0002', 'Home insurance now!', 'HM0001', 2, 2,15000, 75000,0,0, '2013/11/5', '2013/11/10'),
('CM0003', 'My Motocycle insurance!', 'AC0003', 3, 3,50000, 250000,0,0, '2013/12/1', '2013/12/15'),
('CM0004', 'My Business insurance!', 'BN0005', 4, 4,66000, 100000,0,0, '2013/7/6', '2013/8/6')

